package db;

import static db.queries.core.QueriesEnvironment.genInsertPattern;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import core.common.Configurations;
import core.exceptions.DBAccessException;
import core.objects.Author;
import core.objects.Book;
import core.objects.Category;
import core.objects.ObjectWithID;
import core.objects.Publisher;
import core.pool.BoundedPool;
import core.pool.iPool;
import db.dbTables.AbstractDBTable;
import db.dbTables.DbColumn;
import db.queries.core.QueriesEnvironment;

public class DBHandler {

    static iPool<DBConnection> dbConnectionPool = null;
    
    static void openConnectionPool() throws Exception {
    	dbConnectionPool = new BoundedPool<DBConnection>(
        		Configurations.getConnectionPoolCount(), new DBConnectionFactory());
    }
    
    
    public DBHandler() {
    }

    /**
     * Initialize the DB according to the SQL script
     *
     * @throws SQLException
     * @throws IOException
     */
    public void initializeDB() throws SQLException, IOException {
        DBConnection con = dbConnectionPool.get();
        con.executeSQLScript("/resources/loan-a-book_create_db.sql");
        dbConnectionPool.putInPool(con);
    }

    /**
     * Executes the given select query
     *
     * @param query
     * @return
     */
    public static ResultSet executeQuery(IQueryBuilder query) throws SQLException {
        DBConnection con = dbConnectionPool.get();
        
        try {
	        PreparedStatement statement = con.createPreparedStatement(query.buildQuery());
	        query.setParams(statement);
	        ResultSet rs = statement.executeQuery();
	        return rs;
        } finally {
        	dbConnectionPool.putInPool(con);
        }
    }
    
    public static void executeUpdateBatch(IQueryBuilder query) throws SQLException {
        DBConnection con = dbConnectionPool.get();
        
        try {
        	con.beginTransaction();
	        PreparedStatement statement = con.createPreparedStatement(query.buildQuery());
	        query.setParams(statement);
	        con.executePreparedStatementBatch();
        	con.commitTransaction();
        } catch (SQLException e) {
        	con.cancelTransaction();
        	throw e;
        } finally {
        	dbConnectionPool.putInPool(con);
        }
    }
    
    /**
     * Gets the db connection pool
     *
     * @return
     */
    public static iPool<DBConnection> getPool() {
        return dbConnectionPool;
    }

    /**
     * Creates and executes the insertion of base object collection to the DB
     * table
     *
     * @param dbTable
     * @param objSet
     * @throws SQLException
     */
    public static void createAndExecuteBatchUpdate(AbstractDBTable dbTable, Collection<? extends ObjectWithID> objSet) throws SQLException {
        if (objSet == null) {
            return;
        }
        DBConnection con = dbConnectionPool.get();
        
        try {
        	con.beginTransaction();
	        // create an array of all of the columns except for id which is always auto generated
	        List<DbColumn> dbcList = dbTable.getDbColumns();
	        dbcList.remove(dbTable.getIDColumn());
	        DbColumn[] dbcArray = dbcList.toArray(new DbColumn[0]);
	
	        PreparedStatement preSt = con.createPreparedStatement(genInsertPattern(dbcArray));
	        QueriesEnvironment.addBaseObjectsToPreSt(preSt, dbcArray, objSet);
	        con.executePreparedStatementBatch();
        	con.commitTransaction();
        } catch (SQLException e) {
        	con.cancelTransaction();
        	throw e;
        } finally {
        	dbConnectionPool.putInPool(con);
        }
    }

    /**
     * Create and execute insertion of cross table
     *
     * @param dbTable
     * @param id
     * @param objSet
     * @throws SQLException
     */
    public static void createAndExecuteCrossBatchUpdate(AbstractDBTable dbTable, int id, Collection<? extends ObjectWithID> objSet) throws SQLException {
        if (objSet == null) {
            return;
        }
        DBConnection con = dbConnectionPool.get();
        try {
        	con.beginTransaction();
	        // create an array of all of the columns except for id which is always auto generated
	        List<DbColumn> dbcList = dbTable.getDbColumns();
	        dbcList.remove(dbTable.getIDColumn());
	        		
	        DbColumn[] patternDbcArray = dbcList.toArray(new DbColumn[0]);
	        PreparedStatement preSt = con.createPreparedStatement(genInsertPattern(patternDbcArray).replaceFirst("\\?", "" + id));
	
	        dbcList.remove(0);
	        DbColumn[] insertDbcArray = dbcList.toArray(new DbColumn[0]);
	        QueriesEnvironment.addBaseObjectsIdsToPreSt(preSt, insertDbcArray, objSet);
	        con.executePreparedStatementBatch();
        	con.commitTransaction();
        } catch (SQLException e) {
        	con.cancelTransaction();
        	throw e;
        } finally {
        	dbConnectionPool.putInPool(con);
        }
    }   
    	
    /**
     * Insert all of the available base objects from the parsing environment to
     * the DB.
     *
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
	public void constructDB(IConstructDBProgress progress) throws SQLException, DBAccessException {
    	
    	int[] progresses = new int[] { 67, 69, 70, 76, 80, 85, 90, 95, 100 };
    	int i = 0;
    	
        //Insert values into Category table
    	if (progress != null) progress.storingData("Storing categories..");
        createAndExecuteBatchUpdate(QueriesEnvironment.getCategoryTable(), parsing.yago.ParsingEnvironment.getCategories().values());
        parsing.yago.ParsingEnvironment.getCategories().clear();
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into Publisher table
    	if (progress != null) progress.storingData("Storing publishers...");
        createAndExecuteBatchUpdate(QueriesEnvironment.getPublisherTable(), parsing.yago.ParsingEnvironment.getPublishers().values());
        parsing.yago.ParsingEnvironment.getPublishers().clear();
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into Country table
    	if (progress != null) progress.storingData("Storing countries");
        createAndExecuteBatchUpdate(QueriesEnvironment.getCountryTable(), parsing.yago.ParsingEnvironment.getCountries().values());
        parsing.yago.ParsingEnvironment.getCountries().clear();
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into city table
    	if (progress != null) progress.storingData("Storing cities...");
        createAndExecuteBatchUpdate(QueriesEnvironment.getCityTable(), parsing.yago.ParsingEnvironment.getCities().values());
        parsing.yago.ParsingEnvironment.getCities().clear();
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into author table
    	if (progress != null) progress.storingData("Storing authors...");
        createAndExecuteBatchUpdate(QueriesEnvironment.getAuthorTable(), parsing.yago.ParsingEnvironment.getWriters().values());
        parsing.yago.ParsingEnvironment.getWriters().clear();
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into book table
    	if (progress != null) progress.storingData("Storing books...");
        createAndExecuteBatchUpdate(QueriesEnvironment.getBookTable(), parsing.yago.ParsingEnvironment.getBooks().values());
    	if (progress != null) progress.progress(progresses[i++]);
    	
        //Insert values into bookVsWriter table
    	if (progress != null) progress.storingData("Connecting books to authors...");
    	((IDBAccess<Book>)DBAccessEngine.Book()).insertAll(parsing.yago.ParsingEnvironment.getBooks().values(), new Author(0, ""));
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into bookVsCategory table
    	if (progress != null) progress.storingData("Connecting books to categories...");
    	((IDBAccess<Book>)DBAccessEngine.Book()).insertAll(parsing.yago.ParsingEnvironment.getBooks().values(), new Category(0, ""));
    	if (progress != null) progress.progress(progresses[i++]);

        //Insert values into bookVsPublisher table
    	if (progress != null) progress.storingData("Connecting books to publishers...");
    	((IDBAccess<Book>)DBAccessEngine.Book()).insertAll(parsing.yago.ParsingEnvironment.getBooks().values(), new Publisher(0, ""));
    	if (progress != null) progress.progress(progresses[i++]);

        parsing.yago.ParsingEnvironment.getBooks().clear();
        
        if (progress != null)
        	progress.done();

    }

    /**
     * Converts a resultSet to BaseObject list
     *
     * @param rs
     * @param dbTable
     * @return
     */
    public static List<? extends ObjectWithID> convertResultSetToBaseObjectList(ResultSet rs, AbstractDBTable dbTable) {
        List<ObjectWithID> ls = new ArrayList<>();
        try {
            while (rs.next()) {
                ls.add(dbTable.convertSqlTableToBaseObject(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
        	try {
        		if (rs != null)
					rs.close();
			} catch (SQLException exception) {
	            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, exception);
			}
        }
        return ls;
    }
}
